package cn.itcast.travel.dao.impl;

import cn.itcast.travel.dao.FavoriteDao;
import cn.itcast.travel.domain.Favorite;
import cn.itcast.travel.domain.Route;
import cn.itcast.travel.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class FavoriteDaoImp implements FavoriteDao {
    private JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public Favorite findByRidAndUid(int rid, int uid) {
        Favorite favorite=null;
        try{
            String sql="select * from tab_favorite where rid=? and uid=?";
            favorite= jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Favorite>(Favorite.class), rid, uid);
        }catch (Exception e){
            e.printStackTrace();
        }

        return favorite;
    }

    @Override
    public int findCountByRid(int rid) {
         String sql="select count(*) from tab_favorite where rid=?";
        return jdbcTemplate.queryForObject(sql,Integer.class,rid);
    }

    @Override
    public void add(int anInt, int parseInt) {
        String sql="insert into tab_favorite values(?,?,?)";
         jdbcTemplate.update(sql,anInt,new Date(),parseInt);
    }

    @Override
    public List<Route> myFavorite(int start, int pageSize, int uid) {
        String sql="SELECT tr.* FROM tab_user u LEFT JOIN tab_favorite f ON u.uid = f.uid LEFT JOIN tab_route tr ON f.rid = tr.rid where u.uid=? limit ? ,?";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Route>(Route.class),uid,start,pageSize);
    }

    @Override
    public int findCountByUid(int uid) {
        String sql="SELECT COUNT(*) FROM tab_user u LEFT JOIN tab_favorite f ON u.uid = f.uid LEFT JOIN tab_route tr ON f.rid = tr.rid where u.uid=? ";
        return jdbcTemplate.queryForObject(sql,Integer.class,uid);
    }

    @Override
    public int findCountByRse(String rname, int sprice, int eprice) {
        String sql="select count(*) from tab_route where 1=1 ";
        StringBuilder stringBuilder=new StringBuilder(sql);
        List params=new ArrayList();
        if (rname!=null){
            stringBuilder.append(" and rname like ?");
            params.add("%"+rname+"%");
        }
        if (sprice!=0&&eprice!=0){
            stringBuilder.append(" and  price BETWEEN ? and ? ");
            params.add(sprice);
            params.add(eprice);
        }else {
            if (sprice!=0){
                stringBuilder.append(" and price > ? ");
                params.add(sprice);
            }
            if (eprice!=0){
                stringBuilder.append(" and price < ? ");
                params.add(eprice);
            }
        }
        sql=stringBuilder.toString();
        return jdbcTemplate.queryForObject(sql,Integer.class,params.toArray());
    }

    @Override
    public List<Route> findFavoriteQuery(int start, String rname, int sprice, int eprice, int pageSize) {
        String sql="select * from tab_route where 1=1 ";
        StringBuilder stringBuilder=new StringBuilder(sql);
        List params=new ArrayList();
        if (rname!=null){
            stringBuilder.append(" and rname like ?");
            params.add("%"+rname+"%");
        }
        if (sprice!=0&&eprice!=0){
            stringBuilder.append(" and  price BETWEEN ? and ? ");
            params.add(sprice);
            params.add(eprice);
        }else {
            if (sprice!=0){
                stringBuilder.append(" and price > ? ");
                params.add(sprice);
            }
            if (eprice!=0){
                stringBuilder.append(" and price < ? ");
                params.add(eprice);
            }
        }
        stringBuilder.append(" order by count desc");
        stringBuilder.append(" limit ?,? ");
        params.add(start);
        params.add(pageSize);
        sql=stringBuilder.toString();
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Route>(Route.class),params.toArray());
    }
    // SELECT * FROM tab_route WHERE   rname LIKE '%西安%' AND (price BETWEEN 2000 and 3000) ORDER BY count DESC LIMIT 0,8

}
